﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Collections;
using System.Configuration;

/// <summary>
/// Summary description for Students
/// </summary>
[DataObject(true)]
public static class StudentDataSource
{

    [DataObjectMethod(DataObjectMethodType.Select)]
    public static IEnumerable GetStudents()
    {
        string SqlString = "SELECT Students.Student_ID, First_Name, Last_Name, GPA, Email, StudentMembers.Faculty_ID FROM Students LEFT JOIN StudentMembers ON Students.Student_ID = StudentMembers.Student_ID";
        
        SqlCommand CmdSelect = new SqlCommand(SqlString, new SqlConnection(GetConnectionString()));
        
        CmdSelect.Connection.Open();
        SqlDataReader DR = CmdSelect.ExecuteReader(CommandBehavior.CloseConnection);
        return DR;
    }

    [DataObjectMethod(DataObjectMethodType.Insert)]
    public static void InsertStudent(int id, string fname, string lname, double gpa, string email, int Faculty_id, int Student_ID, string First_Name, string Last_Name)
    {
        String sp1 = "AddStudentData";
        String sp2 = "AddStudentID";

        SqlCommand cmd2 = new SqlCommand(sp2, new SqlConnection(GetConnectionString()));
        cmd2.CommandType = CommandType.StoredProcedure;

        cmd2.Parameters.Add(new SqlParameter("@Student_ID", id));
        cmd2.Parameters.Add(new SqlParameter("@Faculty_ID",Faculty_id));

        cmd2.Connection.Open();
        cmd2.ExecuteNonQuery();
        cmd2.Connection.Close();

        SqlCommand cmd1= new SqlCommand(sp1, new SqlConnection(GetConnectionString()));
        cmd1.CommandType = CommandType.StoredProcedure;
   
        cmd1.Parameters.Add(new SqlParameter("@Student_ID", id));
        cmd1.Parameters.Add(new SqlParameter("@Fname", First_Name));
        cmd1.Parameters.Add(new SqlParameter("@Lname", Last_Name));
        cmd1.Parameters.Add(new SqlParameter("@gpa", gpa));
        cmd1.Parameters.Add(new SqlParameter("@email", email));
  
        cmd1.Connection.Open();
        cmd1.ExecuteNonQuery();
        cmd1.Connection.Close(); 
        
    }

    private static string GetConnectionString()
    {
        return ConfigurationManager.ConnectionStrings["EC AssignmentConnectionString"].ConnectionString;
    }
}